import numpy as np
import xlwings as xw
import re

is_visible = False
app = 0
wb = 0

def excel_open():
    global app
    global wb
    app=xw.App(visible=is_visible,add_book=False)
    wb=app.books.open(r'maker.xlsx')

def excel_close():
    global app
    global wb
    wb.close()
    app.quit()
    
def get_rows_columns(sheet):
    info = sheet.used_range
    nrows = info.last_cell.row
    ncolumns = info.last_cell.column
    return nrows, ncolumns

def get_direction(list_direction):
    global wb

    sht = wb.sheets['direction']
    rows, cloumns = get_rows_columns(sht)
    for row in range(2, rows+1):
         row_str = str(row)
         list_direction.append(sht.range('B' + row_str).value)

def get_direction_num(row=2):
    global wb
    list_dirction = []
    sht = wb.sheets['classes']
    direction_num = sht.range('G' + str(row)).value
    if type(direction_num) == float:
        list_dirction.append(str(int(direction_num)))
    elif direction_num is not None:
        list_dirction = re.split(r'[\u3001|\uff0c|\uff1b|;,\s]\s*',direction_num)
    return list_dirction

def get_fields(list_fields):
    global wb
    sht = wb.sheets['fields']
    rows, cloumns = get_rows_columns(sht)
    for row in range(2, rows+1):
         row_str = str(row)
         list_fields.append(sht.range('B' + row_str).value)

def get_field_num(row=2):
    global wb
    list_field = []
    sht = wb.sheets['classes']
    field_num = sht.range('K' + str(row)).value
    if type(field_num) == float:
        list_field.append(str(int(field_num)))
    elif field_num is not None:
        list_field = re.split(r'[\u3001|\uff0c|\uff1b|;,\s]\s*',field_num)
    return list_field

def get_stage(row=2):
    global wb
    sht = wb.sheets['classes']
    stage = sht.range('J' + str(row)).value
    return stage

def get_class_name(row=2):
    global wb
    sht = wb.sheets['classes']
    class_name = sht.range('A' + str(row)).value
    return class_name

def get_target(row=2):
    global wb
    sht = wb.sheets['classes']
    target = sht.range('F' + str(row)).value
    return target

def get_duty(row=2):
    global wb
    sht = wb.sheets['classes']
    duty = sht.range('M' + str(row)).value
    return duty

def get_evaluation_item_rows():
    return 12

def get_evaluation_result_rows():
    return 4

def get_evaluation_item(row):
    global wb
    sht = wb.sheets['evaluation']
    items = []
    for i in range(0,get_evaluation_item_rows()):
        items.append(sht.range('B' + str(row+2)).value)
    return items[row]

def get_item_result(sht, item, who):
    global wb
    sheet = wb.sheets[sht]
    res = sheet.range(chr(72+item) + str(who)).value
    return res

def get_teacher_say(sht, who=2):
    global wb
    sheet = wb.sheets[sht]
    res = sheet.range('G' + str(who)).value
    return res

def get_student_number(list_1, list_2):
    global wb
    sheet = wb.sheets['student_info']
    for i in range(0, len(list_1)):
        list_2.append(sheet.range('B' + str(list_1[i]+1)).value)
    
def get_student_name(list_1, list_2):
    global wb
    sheet = wb.sheets['student_info']
    for i in range(0, len(list_1)):
        list_2.append(sheet.range('C' + str(list_1[i]+1)).value)
        
def get_student_number(sht, who=2):
    global wb
    sheet = wb.sheets[sht]
    res = sheet.range('B' + str(who)).value
    return res

def get_student_name(sht, who=2):
    global wb
    sheet = wb.sheets[sht]
    res = sheet.range('C' + str(who)).value
    return res

def get_date(sht, who=2):
    global wb
    sheet = wb.sheets[sht]
    res = sheet.range('D' + str(who)).value
    res = str(int(res))
    return res

def get_class_row(sheet,who=2):
    res = 0
    class_number = int(wb.sheets[sheet].range('E' + str(who)).value)
    sht = wb.sheets['classes']
    rows, cloumns = get_rows_columns(sht)
    for i in range(0, rows):
        pos = i+2
        v = sht.range('B' + str(pos)).value
        if v is None:
            break
        v   = str(int(v))
        if v == str(class_number):
            res = sht.range('B' + str(pos)).row
    return res